﻿using System.Web.Security;
using System;
using System.Configuration.Provider;
using System.Configuration;
using System.Web.Configuration;
using MySql.Data.MySqlClient;
using System.Data.Common;
using System.Data;
using System.Security.Cryptography;
using System.Text;
using System.Collections.Specialized;
using System.Globalization;
using Reviewer.Utilities;
using Reviewer.Security.Data;
using Reviewer.Utilities.Data;

namespace Reviewer.Security
{
    /// <summary>
    /// Reviewer membership provider
    /// </summary>
    public sealed class MembershipProvider : System.Web.Security.MembershipProvider
    {
        #region methods (static)
        private static string GetConfigValue(string configValue, string defaultValue)
        {
            if (String.IsNullOrEmpty(configValue))
            {
                return defaultValue;
            }

            return configValue;
        }
        private static byte[] HexToByte(string hexString)
        {
            byte[] returnBytes = new byte[hexString.Length / 2];
            for (int i = 0; i < returnBytes.Length; i++)
            {
                returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            }
            return returnBytes;
        }
        /// <summary>
        /// Writes to event log.
        /// </summary>
        /// <param name="e">The e.</param>
        /// <param name="action">The action.</param>
        /// <remarks>
        /// WriteToEventLog
        ///   A helper function that writes exception detail to the event log. Exceptions
        /// are written to the event log as a security measure to avoid private database
        /// details from being returned to the browser. If a method does not return a status
        /// or boolean indicating the action succeeded or failed, a generic exception is also 
        /// thrown by the caller.
        /// </remarks>
        private static void WriteToEventLog(Exception e, string action)
        {
            //TODO: Add error logging
            System.Diagnostics.Trace.TraceError("MembershipProvider/{0} caused an error: {1}", action, e.ToString());
            return;
        }
        #endregion

        #region fields (private)
        private int newPasswordLength = 8;
        private string exceptionMessage = "An exception occurred.";
        private string connectionString;
        private MachineKeySection machineKey;
        private bool writeExceptionsToEventLog;

        private string applicationName;
        private bool enablePasswordReset;
        private bool enablePasswordRetrieval;
        private bool requiresQuestionAndAnswer;
        private bool requiresUniqueEmail;
        private int maxInvalidPasswordAttempts;
        private int passwordAttemptWindow;
        private MembershipPasswordFormat passwordFormat;
        private int minRequiredNonAlphanumericCharacters;
        private int minRequiredPasswordLength;
        private string passwordStrengthRegularExpression;
        #endregion

        #region properties (public)
        /// <summary>
        /// Gets or sets a value indicating whether [write exceptions to event log].
        /// </summary>
        /// <value>
        /// 	<c>true</c> if [write exceptions to event log]; otherwise, <c>false</c>.
        /// </value>
        public bool WriteExceptionsToEventLog
        {
            get { return writeExceptionsToEventLog; }
            set { writeExceptionsToEventLog = value; }
        }

        /// <summary>
        /// The name of the application using the custom membership provider.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// The name of the application using the custom membership provider.
        /// </returns>
        public override string ApplicationName
        {
            get { return applicationName; }
            set { applicationName = value; }
        }

        /// <summary>
        /// Indicates whether the membership provider is configured to allow users to reset their passwords.
        /// </summary>
        /// <value></value>
        /// <returns>true if the membership provider supports password reset; otherwise, false. The default is true.
        /// </returns>
        public override bool EnablePasswordReset
        {
            get { return enablePasswordReset; }
        }

        /// <summary>
        /// Indicates whether the membership provider is configured to allow users to retrieve their passwords.
        /// </summary>
        /// <value></value>
        /// <returns>true if the membership provider is configured to support password retrieval; otherwise, false. The default is false.
        /// </returns>
        public override bool EnablePasswordRetrieval
        {
            get { return enablePasswordRetrieval; }
        }

        /// <summary>
        /// Gets a value indicating whether the membership provider is configured to require the user to answer a password question for password reset and retrieval.
        /// </summary>
        /// <value></value>
        /// <returns>true if a password answer is required for password reset and retrieval; otherwise, false. The default is true.
        /// </returns>
        public override bool RequiresQuestionAndAnswer
        {
            get { return requiresQuestionAndAnswer; }
        }

        /// <summary>
        /// Gets a value indicating whether the membership provider is configured to require a unique e-mail address for each user name.
        /// </summary>
        /// <value></value>
        /// <returns>true if the membership provider requires a unique e-mail address; otherwise, false. The default is true.
        /// </returns>
        public override bool RequiresUniqueEmail
        {
            get { return requiresUniqueEmail; }
        }

        /// <summary>
        /// Gets the number of invalid password or password-answer attempts allowed before the membership user is locked out.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// The number of invalid password or password-answer attempts allowed before the membership user is locked out.
        /// </returns>
        public override int MaxInvalidPasswordAttempts
        {
            get { return maxInvalidPasswordAttempts; }
        }

        /// <summary>
        /// Gets the number of minutes in which a maximum number of invalid password or password-answer attempts are allowed before the membership user is locked out.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// The number of minutes in which a maximum number of invalid password or password-answer attempts are allowed before the membership user is locked out.
        /// </returns>
        public override int PasswordAttemptWindow
        {
            get { return passwordAttemptWindow; }
        }

        /// <summary>
        /// Gets a value indicating the format for storing passwords in the membership data store.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// One of the <see cref="T:System.Web.Security.MembershipPasswordFormat"/> values indicating the format for storing passwords in the data store.
        /// </returns>
        public override MembershipPasswordFormat PasswordFormat
        {
            get { return passwordFormat; }
        }

        /// <summary>
        /// Gets the minimum number of special characters that must be present in a valid password.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// The minimum number of special characters that must be present in a valid password.
        /// </returns>
        public override int MinRequiredNonAlphanumericCharacters
        {
            get { return minRequiredNonAlphanumericCharacters; }
        }

        /// <summary>
        /// Gets the minimum length required for a password.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// The minimum length required for a password.
        /// </returns>
        public override int MinRequiredPasswordLength
        {
            get { return minRequiredPasswordLength; }
        }

        /// <summary>
        /// Gets the regular expression used to evaluate a password.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// A regular expression used to evaluate a password.
        /// </returns>
        public override string PasswordStrengthRegularExpression
        {
            get { return passwordStrengthRegularExpression; }
        }

        #endregion

        #region methods (public)
        /// <summary>
        /// Initializes the specified name.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="config">The config.</param>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (name == null || name.Length == 0)
            {
                name = "ReviewerMembershipProvider";
            }

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Reviewer membersip provider");
            }

            base.Initialize(name, config);

            applicationName = GetConfigValue(config["applicationName"], System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            maxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"), CultureInfo.InvariantCulture);
            passwordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"), CultureInfo.InvariantCulture);
            minRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"), CultureInfo.InvariantCulture);
            minRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"), CultureInfo.InvariantCulture);
            passwordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""), CultureInfo.InvariantCulture);
            enablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"), CultureInfo.InvariantCulture);
            enablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"), CultureInfo.InvariantCulture);
            requiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"), CultureInfo.InvariantCulture);
            requiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"), CultureInfo.InvariantCulture);
            writeExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"), CultureInfo.InvariantCulture);

            string passwordFormatValue = config["passwordFormat"];
            if (passwordFormatValue == null)
            {
                passwordFormatValue = "Hashed";
            }

            if (!EnumUtility.TryParse<MembershipPasswordFormat>(passwordFormatValue, out passwordFormat, true))
            {
                throw new ProviderException("Password format not supported.");
            }

            ConnectionStringSettings ConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null || String.IsNullOrEmpty(ConnectionStringSettings.ConnectionString.Trim()))
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            connectionString = ConnectionStringSettings.ConnectionString;


            // Get encryption and decryption key information from the configuration.
            Configuration cfg = WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
            machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");

            if (machineKey.ValidationKey.Contains("AutoGenerate"))
            {
                if (PasswordFormat != MembershipPasswordFormat.Clear)
                {
                    throw new ProviderException("Hashed or Encrypted passwords are not supported with auto-generated keys.");
                }
            }
        }

        /// <summary>
        /// Changes the password.
        /// </summary>
        /// <param name="username">The user to update the password for.</param>
        /// <param name="oldPassword">The old password.</param>
        /// <param name="newPassword">The new password.</param>
        /// <returns>
        /// 	<c>true</c> if suceeded otherwise, <c>false</c>.
        /// </returns>
        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            if (!ValidateUser(username, oldPassword))
            {
                return false;
            }

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new MembershipPasswordException("Change password canceled due to new password validation failure.");
                }
            }

            using (MySqlCommand cmd = new MySqlCommand(
                /* UPDATE Members SET Password = ?, LastPasswordChangedDate = ?, Format = ? WHERE Username = ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {1} = @{1}, {2} = @{2}, {3} = @{3} WHERE {4} = @{4} AND {5} = @{5}",
                    MembershipTable.TABLE_NAME
                    , MembershipTable.Password.Name
                    , MembershipTable.LastPasswordChangedDate.Name
                    , MembershipTable.Format.Name
                    , MembershipTable.Username.Name
                    , MembershipTable.ApplicationName.Name)))
            {
                MembershipTable.Password.AddParameter(cmd, EncodePassword(newPassword, PasswordFormat));
                MembershipTable.LastPasswordChangedDate.AddParameter(cmd, DateTime.Now);
                MembershipTable.Format.AddParameter(cmd, (int)PasswordFormat);
                MembershipTable.Username.AddParameter(cmd, username);
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);

                if (ExecuteNonQuery(cmd, "ChangePassword") > 0)
                    return true;
            }

            return false;
        }

        /// <summary>
        /// Changes the password question and answer.
        /// </summary>
        /// <param name="username">The user to change the password question and answer for.</param>
        /// <param name="password">The password.</param>
        /// <param name="newPasswordQuestion">The new password question.</param>
        /// <param name="newPasswordAnswer">The new password answer.</param>
        /// <returns>
        /// 	<c>true</c> if suceeded otherwise, <c>false</c>.
        /// </returns>
        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            if (!ValidateUser(username, password))
            {
                return false;
            }

            using (MySqlCommand cmd = new MySqlCommand(
                /* UPDATE Members SET PasswordQuestion ?, PasswordAnswer = ?, AnswerFormat = ? WHERE Username = ? AND ApplicaitonName = ?*/
                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {1} = @{1}, {2} = @{2}, {3} = @{3} WHERE {4} = @{4} AND {5} = @{5}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.PasswordQuestion.Name
                , MembershipTable.PasswordAnswer.Name
                , MembershipTable.AnswerFormat.Name
                , MembershipTable.Username.Name
                , MembershipTable.ApplicationName.Name)))
            {
                MembershipTable.PasswordQuestion.AddParameter(cmd, newPasswordQuestion);
                MembershipTable.PasswordAnswer.AddParameter(cmd, EncodePassword(newPasswordAnswer, PasswordFormat));
                MembershipTable.AnswerFormat.AddParameter(cmd, (int)PasswordFormat);
                MembershipTable.Username.AddParameter(cmd, username);
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);

                if (ExecuteNonQuery(cmd, "ChangePasswordQuestionAndAnswer") > 0)
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// Adds a new membership user to the data source.
        /// </summary>
        /// <param name="username">The user name for the new user.</param>
        /// <param name="password">The password for the new user.</param>
        /// <param name="email">The e-mail address for the new user.</param>
        /// <param name="passwordQuestion">The password question for the new user.</param>
        /// <param name="passwordAnswer">The password answer for the new user</param>
        /// <param name="isApproved">Whether or not the new user is approved to be validated.</param>
        /// <param name="providerUserKey">The unique identifier from the membership data source for the user.</param>
        /// <param name="status">A <see cref="T:System.Web.Security.MembershipCreateStatus"/> enumeration value indicating whether the user was created successfully.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the information for the newly created user.
        /// </returns>
        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if (RequiresUniqueEmail && !String.IsNullOrEmpty(GetUserNameByEmail(email)))
            {
                status = MembershipCreateStatus.DuplicateEmail;
                return null;
            }

            MembershipUser u = GetUser(username, false);

            if (u == null)
            {
                DateTime createDate = DateTime.Now;

                if (providerUserKey == null)
                {
                    providerUserKey = Guid.NewGuid();
                }
                else
                {
                    if (!(providerUserKey is Guid))
                    {
                        status = MembershipCreateStatus.InvalidProviderUserKey;
                        return null;
                    }
                }

                using (MySqlCommand cmd = new MySqlCommand(
                    /*
                     * INSERT INTO Members
                     * (Id, Username, Password, Email, PasswordQuestion, PasswordAnswer, IsApproved, Comment, CreationDate, LastPasswordChangedDate
                     * , LastActivityDate, ApplicationName, IsLockedOut, LastLockedOutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart
                     * , FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Format, AnswerFormat)
                     * VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                     */
                    String.Format(CultureInfo.InvariantCulture, "INSERT INTO {0} "
                  + "({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}"
                 + ", {11}, {12}, {13}, {14}, {15}, {16}"
                 + ", {17}, {18}, {19}, {20})"
                 + " VALUES(@{1}, @{2}, @{3}, @{4}, @{5}, @{6}, @{7}, @{8}, @{9}, @{10}, @{11}, @{12}, @{13}, @{14}, @{15}, @{16}, @{17}, @{18}, @{19}, @{20})"
                 , MembershipTable.TABLE_NAME
                 , MembershipTable.Id.Name
                 , MembershipTable.Username.Name
                 , MembershipTable.Password.Name
                 , MembershipTable.Email.Name
                 , MembershipTable.PasswordQuestion.Name
                 , MembershipTable.PasswordAnswer.Name
                 , MembershipTable.IsApproved.Name
                 , MembershipTable.Comment.Name
                 , MembershipTable.CreationDate.Name
                 , MembershipTable.LastPasswordChangedDate.Name
                 , MembershipTable.LastActivityDate.Name
                 , MembershipTable.ApplicationName.Name
                 , MembershipTable.IsLockedOut.Name
                 , MembershipTable.LastLockedOutDate.Name
                 , MembershipTable.FailedPasswordAttemptCount.Name
                 , MembershipTable.FailedPasswordAttemptWindowStart.Name
                 , MembershipTable.FailedPasswordAnswerAttemptCount.Name
                 , MembershipTable.FailedPasswordAnswerAttemptWindowStart.Name
                 , MembershipTable.Format.Name
                 , MembershipTable.AnswerFormat.Name)))
                {
                    MembershipTable.Id.AddParameter(cmd, providerUserKey);
                    MembershipTable.Username.AddParameter(cmd, username);
                    MembershipTable.Password.AddParameter(cmd, EncodePassword(password, PasswordFormat));
                    MembershipTable.Email.AddParameter(cmd, email);
                    MembershipTable.PasswordQuestion.AddParameter(cmd, passwordQuestion);
                    MembershipTable.PasswordAnswer.AddParameter(cmd, EncodePassword(passwordAnswer, PasswordFormat));
                    MembershipTable.IsApproved.AddParameter(cmd, isApproved);
                    MembershipTable.Comment.AddParameter(cmd, "");
                    MembershipTable.CreationDate.AddParameter(cmd, createDate);
                    MembershipTable.LastPasswordChangedDate.AddParameter(cmd, createDate);
                    MembershipTable.LastActivityDate.AddParameter(cmd, createDate);
                    MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                    MembershipTable.IsLockedOut.AddParameter(cmd, false);
                    MembershipTable.LastLockedOutDate.AddParameter(cmd, createDate);
                    MembershipTable.FailedPasswordAttemptCount.AddParameter(cmd, 0);
                    MembershipTable.FailedPasswordAttemptWindowStart.AddParameter(cmd, createDate);
                    MembershipTable.FailedPasswordAnswerAttemptCount.AddParameter(cmd, 0);
                    MembershipTable.FailedPasswordAnswerAttemptWindowStart.AddParameter(cmd, createDate);
                    MembershipTable.Format.AddParameter(cmd, (int)PasswordFormat);
                    MembershipTable.AnswerFormat.AddParameter(cmd, (int)PasswordFormat);

                    try
                    {
                        if (ExecuteNonQuery(cmd, "CreateUser") > 0)
                        {
                            status = MembershipCreateStatus.Success;
                        }
                        else
                        {
                            status = MembershipCreateStatus.UserRejected;
                        }
                    }
                    catch
                    {
                        status = MembershipCreateStatus.ProviderError;
                        throw;
                    }
                }

                return GetUser(username, false);
            }
            else
            {
                status = MembershipCreateStatus.DuplicateUserName;
            }

            return null;
        }

        /// <summary>
        /// Removes a user from the membership data source.
        /// </summary>
        /// <param name="username">The name of the user to delete.</param>
        /// <param name="deleteAllRelatedData">true to delete data related to the user from the database; false to leave data related to the user in the database.</param>
        /// <returns>
        /// true if the user was successfully deleted; otherwise, false.
        /// </returns>
        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* DELETE FROM Members WHERE Username = ? AND Applicationname = ? */
                String.Format(CultureInfo.InvariantCulture, "DELETE FROM {0} WHERE {1} = @{1} AND {2} = @{2}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.Username.Name
                , MembershipTable.ApplicationName.Name)))
            {
                MembershipTable.Username.AddParameter(cmd, username);
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);

                if (ExecuteNonQuery(cmd, "DeleteUser") > 0)
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// Gets a collection of all the users in the data source in pages of data.
        /// </summary>
        /// <param name="pageIndex">The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">The total number of matched users.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified by <paramref name="pageIndex"/>.
        /// </returns>
        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            MembershipUserCollection users = new MembershipUserCollection();

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Count(*) FROM Members WHERE ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT Count(*) FROM {0} WHERE {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);

                totalRecords = Convert.ToInt32(ExecuteScalar(cmd, "GetAllUsers"), CultureInfo.InvariantCulture);

                if (totalRecords <= 0) { return users; }

                cmd.CommandText =
                    /* SELECT PKID, Username, Email, PasswordQuestion,
                     * Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate,
                     * LastActivityDate, LastPasswordChangedDate, LastLockedOutDate
                     * FROM Members
                     * WHERE ApplicationName = ? 
                     * ORDER BY Username Asc */
                    String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {2}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}"
                + " FROM {0} WHERE {1} = @{1} ORDER BY {2} ASC"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name
                , MembershipTable.Id.Name
                , MembershipTable.Email.Name
                , MembershipTable.PasswordQuestion.Name
                , MembershipTable.Comment.Name
                , MembershipTable.IsApproved.Name
                , MembershipTable.IsLockedOut.Name
                , MembershipTable.CreationDate.Name
                , MembershipTable.LastLoginDate.Name
                , MembershipTable.LastActivityDate.Name
                , MembershipTable.LastPasswordChangedDate.Name
                , MembershipTable.LastLockedOutDate.Name);

                using (IDataReader reader = ExecuteReader(cmd, "GetAllUsers"))
                {
                    int counter = 0;
                    int startIndex = pageSize * pageIndex;
                    int endIndex = startIndex + pageSize - 1;

                    while (reader.Read())
                    {
                        if (counter >= startIndex)
                        {
                            MembershipUser u = GetUserFromReader(reader);
                            users.Add(u);
                        }

                        if (counter >= endIndex)
                        {
                            cmd.Cancel();
                        }

                        counter++;
                    }
                }
            }

            return users;
        }

        /// <summary>
        /// Gets the number of users currently accessing the application.
        /// </summary>
        /// <returns>
        /// The number of users currently accessing the application.
        /// </returns>
        public override int GetNumberOfUsersOnline()
        {
            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Count(*) FROM Members WHERE LastActivityDate > ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT Count(*) FROM {0} WHERE {2} > @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.LastActivityDate.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.LastActivityDate.AddParameter(cmd, compareTime);

                return Convert.ToInt32(ExecuteScalar(cmd, "GetNumberOfUsersOnline"), CultureInfo.InvariantCulture);
            }
        }

        /// <summary>
        /// Gets the password for the specified user name from the data source.
        /// </summary>
        /// <param name="username">The user to retrieve the password for.</param>
        /// <param name="answer">The password answer for the user.</param>
        /// <returns>
        /// The password for the specified user name.
        /// </returns>
        public override string GetPassword(string username, string answer)
        {
            if (!EnablePasswordRetrieval)
            {
                throw new ProviderException("Password Retrieval Not Enabled.");
            }

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Password, PasswordAnswer, IsLockedOut, AnswerFormat, Format FROM Members WHERE Username = ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {4}, {5}, {6}, {7} FROM {0} WHERE {2} = @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name
                , MembershipTable.Password.Name
                , MembershipTable.PasswordAnswer.Name
                , MembershipTable.IsLockedOut.Name
                , MembershipTable.AnswerFormat.Name
                , MembershipTable.Format.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, username);

                using (IDataReader reader = ExecuteReader(cmd, "GetPassword"))
                {
                    if (reader.Read())
                    {
                        if (reader.GetBoolean(2))
                        {
                            throw new MembershipPasswordException("The supplied user is locked out.");
                        }

                        string password = reader.GetString(reader.GetOrdinal(MembershipTable.Password.Name));
                        string passwordAnswer = reader.GetString(reader.GetOrdinal(MembershipTable.PasswordAnswer.Name));
                        MembershipPasswordFormat answerFormat = PasswordFormat;
                        EnumUtility.TryParse<MembershipPasswordFormat>(reader.GetInt32(reader.GetOrdinal(MembershipTable.AnswerFormat.Name)), out answerFormat, PasswordFormat);
                        MembershipPasswordFormat format = PasswordFormat;
                        EnumUtility.TryParse<MembershipPasswordFormat>(reader.GetInt32(reader.GetOrdinal(MembershipTable.Format.Name)), out format, PasswordFormat);

                        if (answerFormat == MembershipPasswordFormat.Hashed || format == MembershipPasswordFormat.Hashed)
                        {
                            throw new ProviderException("Cannot retrieve Hashed passwords.");
                        }

                        if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer, answerFormat))
                        {
                            UpdateFailureCount(username, "passwordAnswer");

                            throw new MembershipPasswordException("Incorrect password answer.");
                        }

                        if (answerFormat == MembershipPasswordFormat.Encrypted)
                        {
                            password = UnEncodePassword(password, format);
                        }

                        return password;
                    }
                    else
                    {
                        throw new MembershipPasswordException("The supplied user name is not found.");
                    }
                }
            }
        }

        /// <summary>
        /// Gets information from the data source for a user. Provides an option to update the last-activity date/time stamp for the user.
        /// </summary>
        /// <param name="username">The name of the user to get information for.</param>
        /// <param name="userIsOnline">true to update the last-activity date/time stamp for the user; false to return user information without updating the last-activity date/time stamp for the user.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the data source.
        /// </returns>
        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT PKID, Username, Email, PasswordQuestion,
                 * Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate,
                 * LastActivityDate, LastPasswordChangedDate, LastLockedOutDate
                 * FROM Members WHERE Username = ? AND ApplicationName = ? */
                 String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {2}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13} FROM {0} WHERE {2} = @{2} AND {1} = @{1}"
                 , MembershipTable.TABLE_NAME
                 , MembershipTable.ApplicationName.Name
                 , MembershipTable.Username.Name
                 , MembershipTable.Id.Name
                 , MembershipTable.Email.Name
                 , MembershipTable.PasswordQuestion.Name
                 , MembershipTable.Comment.Name
                 , MembershipTable.IsApproved.Name
                 , MembershipTable.IsLockedOut.Name
                 , MembershipTable.CreationDate.Name
                 , MembershipTable.LastLoginDate.Name
                 , MembershipTable.LastActivityDate.Name
                 , MembershipTable.LastPasswordChangedDate.Name
                 , MembershipTable.LastLockedOutDate.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, username);

                MembershipUser u = null;
                using (IDataReader reader = ExecuteReader(cmd, "GetUser"))
                {
                    if (reader.Read())
                    {
                        u = GetUserFromReader(reader);

                        if (userIsOnline)
                        {
                            using (MySqlCommand updateCmd = new MySqlCommand(
                                /* UPDATE Members SET LastActivityDate = ? WHERE Username = ? AND Applicationname = ? */
                                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @{3} WHERE {2} = @{2} AND {1} = @{1}"
                                , MembershipTable.TABLE_NAME
                                , MembershipTable.ApplicationName.Name
                                , MembershipTable.Username.Name
                                , MembershipTable.LastActivityDate.Name)))
                            {
                                MembershipTable.ApplicationName.AddParameter(updateCmd, applicationName);
                                MembershipTable.Username.AddParameter(updateCmd, username);
                                MembershipTable.LastActivityDate.AddParameter(updateCmd, DateTime.Now);

                                ExecuteNonQuery(updateCmd, "GetUser");
                            }
                        }
                    }
                }
                return u;
            }
        }

        /// <summary>
        /// Gets user information from the data source based on the unique identifier for the membership user. Provides an option to update the last-activity date/time stamp for the user.
        /// </summary>
        /// <param name="providerUserKey">The unique identifier for the membership user to get information for.</param>
        /// <param name="userIsOnline">true to update the last-activity date/time stamp for the user; false to return user information without updating the last-activity date/time stamp for the user.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUser"/> object populated with the specified user's information from the data source.
        /// </returns>
        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT PKID, Username, Email, PasswordQuestion,
                 * Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate,
                 * LastActivityDate, LastPasswordChangedDate, LastLockedOutDate
                 * FROM Members WHERE Id = ? */
                 String.Format(CultureInfo.InvariantCulture, "SELECT {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12} FROM {0} WHERE {1} = @{1}"
                 , MembershipTable.TABLE_NAME
                 , MembershipTable.Id.Name
                 , MembershipTable.Username.Name
                 , MembershipTable.Email.Name
                 , MembershipTable.PasswordQuestion.Name
                 , MembershipTable.Comment.Name
                 , MembershipTable.IsApproved.Name
                 , MembershipTable.IsLockedOut.Name
                 , MembershipTable.CreationDate.Name
                 , MembershipTable.LastLoginDate.Name
                 , MembershipTable.LastActivityDate.Name
                 , MembershipTable.LastPasswordChangedDate.Name
                 , MembershipTable.LastLockedOutDate.Name)))
            {
                MembershipTable.Id.AddParameter(cmd, providerUserKey);

                MembershipUser u = null;
                using (IDataReader reader = ExecuteReader(cmd, "GetUser"))
                {
                    if (reader.Read())
                    {
                        u = GetUserFromReader(reader);

                        if (userIsOnline)
                        {
                            using (MySqlCommand updateCmd = new MySqlCommand(
                                /* UPDATE Members SET LastActivityDate = ? WHERE PKID = ? */
                                /* UPDATE Members SET LastActivityDate = ? WHERE Username = ? AND Applicationname = ? */
                                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {2} = @{2} WHERE {1} = @{1}"
                                , MembershipTable.TABLE_NAME
                                , MembershipTable.Id.Name
                                , MembershipTable.LastActivityDate.Name)))
                            {
                                MembershipTable.Id.AddParameter(updateCmd, providerUserKey);
                                MembershipTable.LastActivityDate.AddParameter(updateCmd, DateTime.Now);

                                ExecuteNonQuery(updateCmd, "GetUser");
                            }
                        }

                        return u;
                    }
                }
            }

            return null;
        }

        /// <summary>
        /// Unlocks the user.
        /// </summary>
        /// <param name="userName">The membership user whose lock status you want to clear.</param>
        /// <returns>
        /// 	<c>true</c> if suceeded otherwise, <c>false</c>.
        /// </returns>
        public override bool UnlockUser(string userName)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* UPDATE Members SET IsLockedOut = False, LastLockedOutDate = ? WHERE Username = ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @{3}, {4} = @{4} WHERE {2} = @{2} AND {1} = {1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name
                , MembershipTable.IsLockedOut.Name
                , MembershipTable.LastLockedOutDate.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, userName);
                MembershipTable.IsLockedOut.AddParameter(cmd, false);
                MembershipTable.LastLockedOutDate.AddParameter(cmd, DateTime.Now);

                if (ExecuteNonQuery(cmd, "UnlockUser") > 0)
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// Gets the user name associated with the specified e-mail address.
        /// </summary>
        /// <param name="email">The e-mail address to search for.</param>
        /// <returns>
        /// The user name associated with the specified e-mail address. If no match is found, return null.
        /// </returns>
        public override string GetUserNameByEmail(string email)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Username FROM Members WHERE Email = ? AND ApplicationName = ?" */
                String.Format(CultureInfo.InvariantCulture, "SELECT {3} FROM {0} WHERE {2} = @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Email.Name
                , MembershipTable.Username.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Email.AddParameter(cmd, email);

                return Convert.ToString(ExecuteScalar(cmd, "GetUserNameByEmail"), CultureInfo.InvariantCulture);
            }
        }

        /// <summary>
        /// Resets a user's password to a new, automatically generated password.
        /// </summary>
        /// <param name="username">The user to reset the password for.</param>
        /// <param name="answer">The password answer for the specified user.</param>
        /// <returns>The new password for the specified user.</returns>
        public override string ResetPassword(string username, string answer)
        {
            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("Password reset is not enabled.");
            }

            if (answer == null && RequiresQuestionAndAnswer)
            {
                UpdateFailureCount(username, "passwordAnswer");

                throw new ProviderException("Password answer required for password reset.");
            }

            string newPassword = System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
                }
            }

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT PasswordAnswer, IsLockedOut, AnswerFormat FROM Members WHERE Username = ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {4}, {5} FROM {0} WHERE {2} = @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name
                , MembershipTable.PasswordAnswer.Name
                , MembershipTable.IsLockedOut.Name
                , MembershipTable.AnswerFormat.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, username);

                using (IDataReader reader = ExecuteReader(cmd, "ResetPassword"))
                {
                    if (reader.Read())
                    {
                        if (reader.GetBoolean(reader.GetOrdinal(MembershipTable.IsLockedOut.Name)))
                        {
                            throw new MembershipPasswordException("The supplied user is locked out.");
                        }

                        string passwordAnswer = reader.GetString(reader.GetOrdinal(MembershipTable.PasswordAnswer.Name));
                        MembershipPasswordFormat format = PasswordFormat;
                        EnumUtility.TryParse<MembershipPasswordFormat>(reader.GetInt32(reader.GetOrdinal(MembershipTable.AnswerFormat.Name)), out format, PasswordFormat);

                        if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer, format))
                        {
                            UpdateFailureCount(username, "passwordAnswer");

                            throw new MembershipPasswordException("Incorrect password answer.");
                        }

                        using (MySqlCommand updateCmd = new MySqlCommand(
                            /* UPDATE Members
                             * SET Password = ?, LastPasswordChangedDate = ?, Format = ?
                             * WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False */
                             String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {4} = @{4}, {5} = @{5}, {6} = @{6} WHERE {2} = @{2} AND {1} = @{1} AND {3} = @{3}"
                             , MembershipTable.TABLE_NAME
                             , MembershipTable.ApplicationName.Name
                             , MembershipTable.Username.Name
                             , MembershipTable.IsLockedOut.Name
                             , MembershipTable.Password.Name
                             , MembershipTable.LastPasswordChangedDate.Name
                             , MembershipTable.Format.Name)))
                        {
                            MembershipTable.ApplicationName.AddParameter(updateCmd, applicationName);
                            MembershipTable.Username.AddParameter(updateCmd, username);
                            MembershipTable.IsLockedOut.AddParameter(updateCmd, false);
                            MembershipTable.Password.AddParameter(updateCmd, EncodePassword(newPassword, PasswordFormat));
                            MembershipTable.LastPasswordChangedDate.AddParameter(updateCmd, DateTime.Now);
                            MembershipTable.Format.AddParameter(updateCmd, (int)PasswordFormat);

                            if (ExecuteNonQuery(updateCmd, "ResetPassword") > 0)
                            {
                                return newPassword;
                            }
                        }
                    }
                }
            }
            throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
        }

        /// <summary>
        /// Updates information about a user in the data source.
        /// </summary>
        /// <param name="user">A <see cref="T:System.Web.Security.MembershipUser"/> object that represents the user to update and the updated information for the user.</param>
        public override void UpdateUser(MembershipUser user)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* UPDATE Members SET Email = ?, Comment = ?, IsApproved = ? WHERE Username = ? AND ApplicationName = ? */
                    String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @{3}, {4} = @{4}, {5} = {5} WHERE {2} = @{2} AND {1} = @{1}"
                    , MembershipTable.TABLE_NAME
                    , MembershipTable.ApplicationName.Name
                    , MembershipTable.Username.Name
                    , MembershipTable.Email.Name
                    , MembershipTable.Comment.Name
                    , MembershipTable.IsApproved.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, user.UserName);
                MembershipTable.Email.AddParameter(cmd, user.Email);
                MembershipTable.Comment.AddParameter(cmd, user.Comment);
                MembershipTable.IsApproved.AddParameter(cmd, user.IsApproved);

                ExecuteNonQuery(cmd, "UpdateUser");
            }
        }

        /// <summary>
        /// Verifies that the specified user name and password exist in the data source.
        /// </summary>
        /// <param name="username">The name of the user to validate.</param>
        /// <param name="password">The password for the specified user.</param>
        /// <returns>
        /// true if the specified username and password are valid; otherwise, false.
        /// </returns>
        public override bool ValidateUser(string username, string password)
        {
            bool isValid = false;
            bool isApproved = false;
            MembershipPasswordFormat format = PasswordFormat;
            string pwd = "";

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Password, IsApproved, Format FROM Members WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False */
                String.Format(CultureInfo.InvariantCulture, "SELECT {4}, {5}, {6} FROM {0} WHERE {2} = @{2} AND {1} = @{1} AND {3} = @{3}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name
                , MembershipTable.IsLockedOut.Name
                , MembershipTable.Password.Name
                , MembershipTable.IsApproved.Name
                , MembershipTable.Format.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, username);
                MembershipTable.IsLockedOut.AddParameter(cmd, false);

                using (IDataReader reader = ExecuteReader(cmd, "ValidateUser"))
                {
                    if (reader.Read())
                    {
                        pwd = reader.GetString(reader.GetOrdinal(MembershipTable.Password.Name));
                        isApproved = reader.GetBoolean(reader.GetOrdinal(MembershipTable.IsApproved.Name));
                        EnumUtility.TryParse<MembershipPasswordFormat>(reader.GetInt32(reader.GetOrdinal(MembershipTable.Format.Name)), out format, PasswordFormat);
                    }
                    else
                    {
                        return false;
                    }
                }
            }

            if (CheckPassword(password, pwd, format))
            {
                if (isApproved)
                {
                    isValid = true;

                    using (MySqlCommand updateCmd = new MySqlCommand(
                        /* UPDATE Members SET LastLoginDate = ? WHERE Username = ? AND ApplicationName = ? */
                        String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @{3} WHERE {2} = @{2} AND {1} = @{1}"
                        , MembershipTable.TABLE_NAME
                        , MembershipTable.ApplicationName.Name
                        , MembershipTable.Username.Name
                        , MembershipTable.LastLoginDate.Name)))
                    {
                        MembershipTable.ApplicationName.AddParameter(updateCmd, applicationName);
                        MembershipTable.Username.AddParameter(updateCmd, username);
                        MembershipTable.LastLoginDate.AddParameter(updateCmd, DateTime.Now);

                        ExecuteNonQuery(updateCmd, "ValidateUser");
                    }
                }
            }
            else
            {
                UpdateFailureCount(username, "password");
            }

            return isValid;
        }

        /// <summary>
        /// Gets a collection of membership users where the user name contains the specified user name to match.
        /// </summary>
        /// <param name="usernameToMatch">The user name to search for.</param>
        /// <param name="pageIndex">The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">The total number of matched users.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified by <paramref name="pageIndex"/>.
        /// </returns>
        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            MembershipUserCollection users = new MembershipUserCollection();

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Count(*) FROM Members WHERE Username LIKE ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT Count(*) FROM {0} WHERE {2} LIKE @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, usernameToMatch);

                totalRecords = Convert.ToInt32(ExecuteScalar(cmd, "FindUsersByName"), CultureInfo.InvariantCulture);
                if (totalRecords <= 0)
                {
                    return users;
                }

                cmd.CommandText =
                    /* SELECT PKID, Username, Email, PasswordQuestion,
                     * Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate,
                     * LastActivityDate, LastPasswordChangedDate, LastLockedOutDate
                     * FROM Members
                     * WHERE Username LIKE ? AND ApplicationName = ? "
                     * ORDER BY Username Asc */
                String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {2}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13} FROM {0} WHERE {2} LIKE @{2} AND {1} = @{1} ORDER BY {2} ASC"
                 , MembershipTable.TABLE_NAME
                 , MembershipTable.ApplicationName.Name
                 , MembershipTable.Username.Name
                 , MembershipTable.Id.Name
                 , MembershipTable.Email.Name
                 , MembershipTable.PasswordQuestion.Name
                 , MembershipTable.Comment.Name
                 , MembershipTable.IsApproved.Name
                 , MembershipTable.IsLockedOut.Name
                 , MembershipTable.CreationDate.Name
                 , MembershipTable.LastLoginDate.Name
                 , MembershipTable.LastActivityDate.Name
                 , MembershipTable.LastPasswordChangedDate.Name
                 , MembershipTable.LastLockedOutDate.Name);

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                using (IDataReader reader = ExecuteReader(cmd, "FindUsersByName"))
                {
                    while (reader.Read())
                    {
                        if (counter >= startIndex)
                        {
                            MembershipUser u = GetUserFromReader(reader);
                            users.Add(u);
                        }

                        if (counter >= endIndex) { cmd.Cancel(); }

                        counter++;
                    }
                }
            }

            return users;
        }

        /// <summary>
        /// Gets a collection of membership users where the e-mail address contains the specified e-mail address to match.
        /// </summary>
        /// <param name="emailToMatch">The e-mail address to search for.</param>
        /// <param name="pageIndex">The index of the page of results to return. <paramref name="pageIndex"/> is zero-based.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">The total number of matched users.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Security.MembershipUserCollection"/> collection that contains a page of <paramref name="pageSize"/><see cref="T:System.Web.Security.MembershipUser"/> objects beginning at the page specified by <paramref name="pageIndex"/>.
        /// </returns>
        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            MembershipUserCollection users = new MembershipUserCollection();

            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT Count(*) FROM Members WHERE Email LIKE ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT Count(*) FROM {0} WHERE {2} LIKE @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Email.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Email.AddParameter(cmd, emailToMatch);

                totalRecords = Convert.ToInt32(ExecuteScalar(cmd, "FindUsersByEmail"), CultureInfo.InvariantCulture);

                if (totalRecords <= 0)
                {
                    return users;
                }

                cmd.CommandText =
                    /* SELECT PKID, Username, Email, PasswordQuestion,
                     * Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate,
                     * LastActivityDate, LastPasswordChangedDate, LastLockedOutDate
                     * FROM Members WHERE Email LIKE ? AND ApplicationName = ? 
                     * ORDER BY Username Asc */
                String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {4}, {2}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13} FROM {0} WHERE {2} LIKE @{2} AND {1} = @{1} ORDER BY {2} ASC"
                 , MembershipTable.TABLE_NAME
                 , MembershipTable.ApplicationName.Name
                 , MembershipTable.Email.Name
                 , MembershipTable.Id.Name
                 , MembershipTable.Username.Name
                 , MembershipTable.PasswordQuestion.Name
                 , MembershipTable.Comment.Name
                 , MembershipTable.IsApproved.Name
                 , MembershipTable.IsLockedOut.Name
                 , MembershipTable.CreationDate.Name
                 , MembershipTable.LastLoginDate.Name
                 , MembershipTable.LastActivityDate.Name
                 , MembershipTable.LastPasswordChangedDate.Name
                 , MembershipTable.LastLockedOutDate.Name);

                int counter = 0;
                int startIndex = pageSize * pageIndex;
                int endIndex = startIndex + pageSize - 1;

                using (IDataReader reader = ExecuteReader(cmd, "FindUsersByEmail"))
                {
                    while (reader.Read())
                    {
                        if (counter >= startIndex)
                        {
                            MembershipUser u = GetUserFromReader(reader);
                            users.Add(u);
                        }

                        if (counter >= endIndex) { cmd.Cancel(); }

                        counter++;
                    }
                }
            }

            return users;
        }
        #endregion

        #region methods (private)
        private int ExecuteNonQuery(MySqlCommand command, string executeProcessName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                command.Connection = connection;
                try
                {
                    connection.Open();

                    return command.ExecuteNonQuery();
                }
                catch (MySqlException e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, executeProcessName);

                        throw new ProviderException(exceptionMessage);
                    }
                    else
                    {
                        throw;
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private object ExecuteScalar(MySqlCommand command, string executeProcessName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                command.Connection = connection;
                try
                {
                    connection.Open();

                    return command.ExecuteScalar();
                }
                catch (MySqlException e)
                {
                    if (WriteExceptionsToEventLog)
                    {
                        WriteToEventLog(e, executeProcessName);

                        throw new ProviderException(exceptionMessage);
                    }
                    else
                    {
                        throw;
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private IDataReader ExecuteReader(MySqlCommand command, string executeProcessName)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            command.Connection = connection;
            try
            {
                connection.Open();

                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (MySqlException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, executeProcessName);

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw;
                }
            }
        }

        private MembershipUser GetUserFromReader(IDataReader reader)
        {
            object providerUserKey = reader.GetValue(reader.GetOrdinal(MembershipTable.Id.Name));
            string username = reader.GetString(reader.GetOrdinal(MembershipTable.Username.Name));
            string email = reader.GetString(reader.GetOrdinal(MembershipTable.Email.Name));
            string passwordQuestion = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.PasswordQuestion.Name), String.Empty);
            string comment = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.Comment.Name), String.Empty);
            bool isApproved = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.IsApproved.Name), false);
            bool isLockedOut = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.IsLockedOut.Name), false);
            
            DateTime creationDate = reader.GetDateTime(reader.GetOrdinal(MembershipTable.CreationDate.Name));

            DateTime lastLoginDate = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.LastLoginDate.Name), DateTime.MinValue);
            DateTime lastActivityDate = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.LastActivityDate.Name), DateTime.MinValue);
            DateTime lastPasswordChangedDate = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.LastPasswordChangedDate.Name), DateTime.MinValue);
            DateTime lastLockedOutDate = DataReaderUtil.GetValue(reader, reader.GetOrdinal(MembershipTable.LastLockedOutDate.Name), DateTime.MinValue);

            MembershipUser u = new MembershipUser(this.Name,
                                                  username,
                                                  providerUserKey,
                                                  email,
                                                  passwordQuestion,
                                                  comment,
                                                  isApproved,
                                                  isLockedOut,
                                                  creationDate,
                                                  lastLoginDate,
                                                  lastActivityDate,
                                                  lastPasswordChangedDate,
                                                  lastLockedOutDate);

            return u;
        }

        private void UpdateFailureCount(string username, string failureType)
        {
            using (MySqlCommand cmd = new MySqlCommand(
                /* SELECT FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart,
                 * FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart
                 * FROM Members WHERE Username = ? AND ApplicationName = ? */
                String.Format(CultureInfo.InvariantCulture, "SELECT {3}, {4}, {5}, {6} FROM {0} WHERE {2} = @{2} AND {1} = @{1}"
                , MembershipTable.TABLE_NAME
                , MembershipTable.ApplicationName.Name
                , MembershipTable.Username.Name
                , MembershipTable.FailedPasswordAttemptCount.Name
                , MembershipTable.FailedPasswordAttemptWindowStart.Name
                , MembershipTable.FailedPasswordAnswerAttemptCount.Name
                , MembershipTable.FailedPasswordAnswerAttemptWindowStart.Name)))
            {
                MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                MembershipTable.Username.AddParameter(cmd, username);

                DateTime windowStart = new DateTime();
                int failureCount = 0;

                using (IDataReader reader = ExecuteReader(cmd, "UpdateFailureCount"))
                {
                    if (reader.Read())
                    {
                        if (failureType == "password")
                        {
                            failureCount = reader.GetInt32(0);
                            windowStart = reader.GetDateTime(1);
                        }

                        if (failureType == "passwordAnswer")
                        {
                            failureCount = reader.GetInt32(2);
                            windowStart = reader.GetDateTime(3);
                        }
                    }
                }

                DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

                if (failureCount == 0 || DateTime.Now > windowEnd)
                {
                    // First password failure or outside of PasswordAttemptWindow. 
                    // Start a new password failure count from 1 and a new window starting now.

                    if (failureType == "password")
                    {
                        cmd.CommandText =
                            /* UPDATE Members SET FailedPasswordAttemptCount = ?, FailedPasswordAttemptWindowStart = ?
                             * WHERE Username = ? AND ApplicationName = ? */
                            String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @Count, {4} = @WindowStart WHERE {2} = @{2} AND {1} = @{1}"
                            , MembershipTable.TABLE_NAME
                            , MembershipTable.ApplicationName.Name
                            , MembershipTable.Username.Name
                            , MembershipTable.FailedPasswordAttemptCount.Name
                            , MembershipTable.FailedPasswordAttemptWindowStart.Name);

                    }

                    if (failureType == "passwordAnswer")
                    {
                        cmd.CommandText =
                            /* UPDATE Members SET FailedPasswordAnswerAttemptCount = ?, FailedPasswordAnswerAttemptWindowStart = ? 
                             * WHERE Username = ? AND ApplicationName = ? */
                            String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @Count, {4} = @WindowStart WHERE {2} = @{2} AND {1} = @{1}"
                            , MembershipTable.TABLE_NAME
                            , MembershipTable.ApplicationName.Name
                            , MembershipTable.Username.Name
                            , MembershipTable.FailedPasswordAnswerAttemptCount.Name
                            , MembershipTable.FailedPasswordAnswerAttemptWindowStart.Name);

                    }

                    cmd.Parameters.Clear();

                    MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                    MembershipTable.Username.AddParameter(cmd, username);
                    cmd.Parameters.Add("@Count", MySqlDbType.Int32).Value = 1;
                    cmd.Parameters.Add("@WindowStart", MySqlDbType.DateTime).Value = DateTime.Now;

                    if (ExecuteNonQuery(cmd, "UpdateFailureCount") < 0)
                    {
                        throw new ProviderException("Unable to update failure count and window start.");
                    }
                }
                else
                {
                    if (failureCount++ >= MaxInvalidPasswordAttempts)
                    {
                        // Password attempts have exceeded the failure threshold. Lock out
                        // the user.

                        cmd.CommandText =
                            /* UPDATE Members SET IsLockedOut = ?, LastLockedOutDate = ? WHERE Username = ? AND ApplicationName = ? */
                            String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @{3}, {4} = @{4} WHERE {2} = @{2} AND {1} = {1}"
                            , MembershipTable.TABLE_NAME
                            , MembershipTable.ApplicationName.Name
                            , MembershipTable.Username.Name
                            , MembershipTable.IsLockedOut.Name
                            , MembershipTable.LastLockedOutDate.Name);

                        cmd.Parameters.Clear();

                        MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                        MembershipTable.Username.AddParameter(cmd, username);
                        MembershipTable.IsLockedOut.AddParameter(cmd, true);
                        MembershipTable.LastLockedOutDate.AddParameter(cmd, DateTime.Now);

                        if (ExecuteNonQuery(cmd, "UpdateFailureCount") < 0)
                        {
                            throw new ProviderException("Unable to lock out user.");
                        }
                    }
                    else
                    {
                        // Password attempts have not exceeded the failure threshold. Update
                        // the failure counts. Leave the window the same.

                        if (failureType == "password")
                        {
                            cmd.CommandText =
                                /* UPDATE Members SET FailedPasswordAttemptCount = ? WHERE Username = ? AND ApplicationName = ? */
                                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @Count WHERE {2} = @{2} AND {1} = @{1}"
                                , MembershipTable.TABLE_NAME
                                , MembershipTable.ApplicationName.Name
                                , MembershipTable.Username.Name
                                , MembershipTable.FailedPasswordAttemptCount.Name);
                        }

                        if (failureType == "passwordAnswer")
                        {
                            cmd.CommandText =
                                /* UPDATE Members SET FailedPasswordAnswerAttemptCount = ? WHERE Username = ? AND ApplicationName = ? */
                                String.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET {3} = @Count WHERE {2} = @{2} AND {1} = @{1}"
                                , MembershipTable.TABLE_NAME
                                , MembershipTable.ApplicationName.Name
                                , MembershipTable.Username.Name
                                , MembershipTable.FailedPasswordAnswerAttemptCount.Name);

                        }

                        cmd.Parameters.Clear();

                        MembershipTable.ApplicationName.AddParameter(cmd, applicationName);
                        MembershipTable.Username.AddParameter(cmd, username);
                        cmd.Parameters.Add("@Count", MySqlDbType.Int32).Value = failureCount;

                        if (ExecuteNonQuery(cmd, "UpdateFailureCount") < 0)
                        {
                            throw new ProviderException("Unable to update failure count.");
                        }
                    }
                }
            }
        }

        private bool CheckPassword(string password, string dbpassword, MembershipPasswordFormat format)
        {
            string pass1 = password;
            string pass2 = dbpassword;

            switch (format)
            {
                case MembershipPasswordFormat.Encrypted:
                    pass2 = UnEncodePassword(dbpassword, format);
                    break;
                case MembershipPasswordFormat.Hashed:
                    pass1 = EncodePassword(password, format);
                    break;
                default:
                    break;
            }

            if (pass1 == pass2)
            {
                return true;
            }

            return false;
        }

        private string EncodePassword(string password, MembershipPasswordFormat format)
        {
            string encodedPassword = password;

            switch (format)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    encodedPassword =
                      Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    HMACSHA1 hash = new HMACSHA1();
                    hash.Key = HexToByte(machineKey.ValidationKey);
                    encodedPassword =
                      Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
                    break;
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return encodedPassword;
        }

        private string UnEncodePassword(string encodedPassword, MembershipPasswordFormat format)
        {
            string password = encodedPassword;

            switch (format)
            {
                case MembershipPasswordFormat.Clear:
                    break;
                case MembershipPasswordFormat.Encrypted:
                    password =
                      Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
                    break;
                case MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Cannot unencode a hashed password.");
                default:
                    throw new ProviderException("Unsupported password format.");
            }

            return password;
        }
        #endregion
    }
}
